﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Threading.Tasks;
using System.IO;
using Google.YouTube;
using Google.GData.YouTube;

namespace KC.Presentation.Mv8CMS
{
	public partial class LiveMonitor : Form
	{
		private const string YouTubeDevKey = @"AI39si7q0Rus3aclueDa9PeJziG3ZZNpCjb5g3cTRvvkfSTTHq-uw9E54qlqgwhBrqr0kGeqAw9qRCkq1K-BI3vLBRiOtA_5uQ";
		private const string YouTubeAppName = @"Social Media Monitor";
		private int LastActivityID = 0;

		private string csMv8 = System.Configuration.ConfigurationManager.ConnectionStrings["MV8"].ConnectionString;
		private string csSocial = System.Configuration.ConfigurationManager.ConnectionStrings["Social"].ConnectionString;

		public LiveMonitor()
		{
			InitializeComponent();
		}
		private void LiveMonitor_Load(object sender, EventArgs e)
		{
			timer1.Start();
			timer2.Start();
			tSpider.Start();
		}
		private void timer1_Tick(object sender, EventArgs e)
		{
			if (cbLive.Checked) Reload();
		}
		private void timer2_Tick(object sender, EventArgs e)
		{
			if (!bgwYoutubeLookups.IsBusy) bgwYoutubeLookups.RunWorkerAsync();
		}
	
		public void Reload()
		{
			LoadActivityGrid();
			LoadSearchGrid();
			Load24hActivityGraph();
			LoadSocialGrid();
		}
		private void DownloadYoutubeActivity()
		{
			if (txtYoutubePass.Text.Trim().Length == 0 || txtYoutubeUser.Text.Trim().Length == 0) return;

			// Download and import YouTube Contacts Activity data
			DataTable dt = KC.DataAccess.Global.Global.CallSql_Query(csSocial, "select max(ThirdPartyDatetime) from ContactActivities");
			DateTime lastClientDate = dt == null || dt.Rows.Count == 0 || dt.Rows[0][0].GetType() != typeof(DateTime) ? DateTime.Today.AddDays(-1) : (DateTime)dt.Rows[0][0];
			YouTubeRequestSettings set = new YouTubeRequestSettings(YouTubeAppName, YouTubeDevKey, txtYoutubeUser.Text, txtYoutubePass.Text);
			YouTubeRequest req = new YouTubeRequest(set);
			var acts = req.GetActivities(lastClientDate);
			try { System.Threading.Tasks.Parallel.ForEach(acts.Entries.ToList(), new ParallelOptions() { MaxDegreeOfParallelism = 8 }, e => ProcessYoutubeActivity(e)); }
			catch (Google.GData.Client.GDataNotModifiedException) { } // Nothing new from Google. Apparently that's an exception?

			string feedUrl = "http://gdata.youtube.com/feeds/api/users/default/inbox";

			// Download Inbox
			YouTubeQuery query = new YouTubeQuery(feedUrl);
			Google.GData.YouTube.YouTubeService service = new YouTubeService(YouTubeAppName, YouTubeDevKey);
			service.setUserCredentials(txtYoutubeUser.Text, txtYoutubePass.Text);
			try {
				MessageFeed messagesFeed = service.GetMessages(query);
				foreach (MessageEntry entry in messagesFeed.Entries)
					Console.WriteLine("YouTube Message from: " + entry.Authors[0].Name + "\r\n" +
						" Subject: " + entry.Title.Text + "\r\n " +
						entry.Summary.Text + "\r\n" +
						" Attached video: " + entry.AlternateUri.Content + "\r\n"); }
			catch { }
			

			//ProfileEntry profile = (ProfileEntry)service.Get("http://gdata.youtube.com/feeds/api/users/Default");

			// Look up profile activity graph data
			//ProcessProfileAnalytics(profile.Links.Where(l => l.AbsoluteUri.Contains("insight.youtube.com")).First().AbsoluteUri); // i.e. http://insight.youtube.com/video-analytics/csvreports?query=tsilb&type=c&starttime=1235865600000&endtime=1326499200000&user_starttime=1325894400000&user_endtime=1326499200000&region=world&token=4dMFah2ohHN47ZuyREU6jaBGa458MTMyNjY3MDg4N0AxMzI2NjY5MDg3&hl=en_US

			//var videoFeed = req.Get<Video>(new Uri("http://gdata.youtube.com/feeds/api/users/Default/uploads"));
			//var Top10 = videoFeed.Entries.OrderByDescending(v => v.ViewCount).ToList();

			//feedUrl = "http://insight.youtube.com/video-analytics/csvreports?query=PlVJ88-zqkI&type=v" +
			//    "&starttime=1296086400000&endtime=1298505600000&user_starttime=1297900800000&user_endtime=1298505600000" +
			//    "&region=world&token=lGYc7XEFNTATxTmseKN_M--c7-V8MTI5ODY3MzQ2Nw==&hl=en_US";
		}
		private void ProcessProfileAnalytics(string url)
		{
			string path = Path.GetTempPath() + @"YoutubeCSV\";
			if (!Directory.Exists(path)) Directory.CreateDirectory(path);
			string zipfile = path + DateTime.Now.ToString("MMddyyyyHHmmss") + ".zip";
			foreach (string file in Directory.GetFiles(path))
				File.Delete(file);

			new System.Net.WebClient().DownloadFile(url, zipfile); // Download ZIP file
			string[] files = KC.DataAccess.IO.Zip.Unzip(zipfile, path); // Unzip CSVs

			// Process the contained CSVs
			foreach (string file in files) {
				string[] lines = File.ReadAllLines(path + file);
				
				// World Views
				if (file.ToLower().Contains("world_views")) {
					for (int i = 1; i < lines.Length; i++) {
						string[] fields = lines[i].Split(',');
						DateTime date = DateTime.Parse(fields[0]).Date;
						KC.DataAccess.Global.Global.CallSql_Query(csSocial, "if (select count(ID) from YoutubeAnalytics where Date='" + date.ToString("M/d/yyyy") + "') = 0 begin \r\n" +
								"insert into YoutubeAnalytics (date, region, channel, views, uniqueusers, unique7, unique30, popularity, comments, favorites, rating1, rating2, rating3, rating4, rating5, subscriptions, unsubscriptions) " +
								"values ('" + date.ToString("MM/dd/yyyy") + "', '" + fields[1] + "','" + fields[2].Replace("'", "''") + "'," + fields[3] + "," + fields[4] + "," + fields[5] + "," + fields[6] + "," + fields[7] + "," + fields[8] +
								"," + fields[9] + "," + fields[10] + "," + fields[11] + "," + fields[12] + "," + fields[13] + "," + fields[14] + "," + fields[15] + "," + fields[16] + ") " +
							"end else begin " +
								"" +
							"end"); } }

				// World Locations

				// World Referrers

				// Demographics


			}
		}

		private void Load24hActivityGraph()
		{
			// Total Activity
			chart1.Series.Clear();
			chart1.Series.Add(new System.Windows.Forms.DataVisualization.Charting.Series("Hits"));
			chart1.Series[0].AxisLabel = "Hits";
			chart1.Series[0].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.Line;
			chart1.Series[0].Color = Color.Blue;
			DataTable dtChart = KC.DataAccess.Global.Global.CallSql_Query(csMv8,
				"select substring(cast(cast(convert(datetime, [datetime], 108) as time) as varchar), 0, 3), count(substring(cast(cast(convert(datetime, [datetime], 108) as time) as varchar), 0, 3)), max(datetime) from activities a \r\n" +
				"inner join sessions s on a.sid=s.id \r\n" +
				"inner join ips i on s.ipid=i.id \r\n" +
				"where datediff(minute, [datetime], getdate()) < 1380 \r\n" +
				"and i.address not like '192.168.%' and address not like '10.%' \r\n" +
				"group by substring(cast(cast(convert(datetime, [datetime], 108) as time) as varchar), 0, 3) \r\n" +
				"order by datediff(minute, max(datetime), getdate())");
			foreach (DataRow dr in dtChart.Rows)
				chart1.Series[0].Points.Add(new System.Windows.Forms.DataVisualization.Charting.DataPoint(double.Parse((string)dr[0]), (double)(int)dr[1]) { AxisLabel = (string)dr[0], IsValueShownAsLabel = false });

			// Humans
			chart1.Series.Add(new System.Windows.Forms.DataVisualization.Charting.Series("Humans"));
			chart1.Series[1].AxisLabel = "Humans";
			chart1.Series[1].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.Line;
			chart1.Series[1].Color = Color.Green;
			dtChart = KC.DataAccess.Global.Global.CallSql_Query(csMv8,
				"select substring(cast(cast(convert(datetime, [datetime], 108) as time) as varchar), 0, 3), count(substring(cast(cast(convert(datetime, [datetime], 108) as time) as varchar), 0, 3)), max(datetime) from activities a \r\n" +
				"inner join sessions s on a.sid=s.id \r\n" +
				"inner join ips i on s.ipid=i.id \r\n" +
				"left outer join blacklist b on a.referrer like '%' + b.domain + '%' " +
				"where datediff(minute, [datetime], getdate()) < 1380 \r\n" +
				"and i.address not like '192.168.%' and address not like '10.%' and i.isspider=0 \r\n" +
				"and i.numproblems = 0 and b.domain is null " +
				"group by substring(cast(cast(convert(datetime, [datetime], 108) as time) as varchar), 0, 3) \r\n" +
				"order by datediff(minute, max(datetime), getdate())");
			foreach (DataRow dr in dtChart.Rows)
				chart1.Series[1].Points.Add(new System.Windows.Forms.DataVisualization.Charting.DataPoint(double.Parse((string)dr[0]), (double)(int)dr[1]) { AxisLabel = (string)dr[0], IsValueShownAsLabel = true });

			// Searches
			chart1.Series.Add(new System.Windows.Forms.DataVisualization.Charting.Series("Searches"));
			chart1.Series[2].AxisLabel = "Searches";
			chart1.Series[2].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.Line;
			chart1.Series[2].Color = Color.FromArgb(128, 64, 64);
			dtChart = KC.DataAccess.Global.Global.CallSql_Query(csMv8,
				"select substring(cast(cast(convert(datetime, [datetime], 108) as time) as varchar), 0, 3), count(substring(cast(cast(convert(datetime, [datetime], 108) as time) as varchar), 0, 3)), max(datetime) from SearchResults \r\n" +
				"where datediff(minute, [datetime], getdate()) < 1380 \r\n" +
				"group by substring(cast(cast(convert(datetime, [datetime], 108) as time) as varchar), 0, 3) \r\n" +
				"order by datediff(minute, max(datetime), getdate())");
			foreach (DataRow dr in dtChart.Rows)
				chart1.Series[2].Points.Add(new System.Windows.Forms.DataVisualization.Charting.DataPoint(double.Parse((string)dr[0]), (double)(int)dr[1]) { AxisLabel = (string)dr[0], IsValueShownAsLabel = true });

			// Banned / Problems
			chart1.Series.Add(new System.Windows.Forms.DataVisualization.Charting.Series("Attacks"));
			chart1.Series[3].AxisLabel = "Attacks";
			chart1.Series[3].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.Line;
			chart1.Series[3].Color = Color.Red;
			dtChart = KC.DataAccess.Global.Global.CallSql_Query(csMv8,
				"select substring(cast(cast(convert(datetime, [datetime], 108) as time) as varchar), 0, 3), count(substring(cast(cast(convert(datetime, [datetime], 108) as time) as varchar), 0, 3)), max(datetime) from activities a\r\n" +
				"inner join sessions s on a.sid=s.id \r\n" +
				"inner join ips i on s.ipid=i.id \r\n" +
				"left outer join blacklist b on a.referrer like '%' + b.domain + '%' " +
				"where datediff(minute, [datetime], getdate()) < 1380 \r\n" +
				"and i.address not like '192.168.%' and address not like '10.%' and i.isspider=0 \r\n" +
				"and (i.numproblems > 0 OR b.domain is not null) " +
				"group by substring(cast(cast(convert(datetime, [datetime], 108) as time) as varchar), 0, 3) \r\n" +
				"order by datediff(minute, max(datetime), getdate())");
			foreach (DataRow dr in dtChart.Rows)
				chart1.Series[3].Points.Add(new System.Windows.Forms.DataVisualization.Charting.DataPoint(double.Parse((string)dr[0]), (double)(int)dr[1]) { AxisLabel = (string)dr[0], IsValueShownAsLabel = false });

			// Spiders
			chart1.Series.Add(new System.Windows.Forms.DataVisualization.Charting.Series("Spiders"));
			chart1.Series[4].AxisLabel = "Spiders";
			chart1.Series[4].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.Line;
			chart1.Series[4].Color = Color.FromArgb(64, 64, 64);
			dtChart = KC.DataAccess.Global.Global.CallSql_Query(csMv8,
				"select substring(cast(cast(convert(datetime, [datetime], 108) as time) as varchar), 0, 3), count(substring(cast(cast(convert(datetime, [datetime], 108) as time) as varchar), 0, 3)), max(datetime) from activities a\r\n" +
				"inner join sessions s on a.sid=s.id \r\n" +
				"inner join ips i on s.ipid=i.id \r\n" +
				"where datediff(minute, [datetime], getdate()) < 1380 \r\n" +
				"and i.address not like '192.168.%' and address not like '10.%' and i.isspider=1 \r\n" +
				"group by substring(cast(cast(convert(datetime, [datetime], 108) as time) as varchar), 0, 3) \r\n" +
				"order by datediff(minute, max(datetime), getdate())");
			foreach (DataRow dr in dtChart.Rows)
				chart1.Series[4].Points.Add(new System.Windows.Forms.DataVisualization.Charting.DataPoint(double.Parse((string)dr[0]), (double)(int)dr[1]) { AxisLabel = (string)dr[0], IsValueShownAsLabel = false });

			chart1.ChartAreas[0].AxisX.MajorGrid.Interval = 1;
			chart1.ChartAreas[0].AxisY.MajorGrid.Interval = 100;
			chart1.ChartAreas[0].AxisX.MajorGrid.LineColor = Color.DarkGray;
			chart1.ChartAreas[0].AxisY.MajorGrid.LineColor = Color.DarkGray;
			chart1.Legends[0].Font = new Font(FontFamily.GenericSerif, 7);
			chart1.Legends[0].Docking = System.Windows.Forms.DataVisualization.Charting.Docking.Bottom;
		}
		private void LoadActivityGrid()
		{
			int TempLastActivityID = 0;
			dgActivities.DataSource = KC.DataAccess.Global.Global.CallSql_Query(csMv8,
				"select top 50 a.ID [Activity], s.ID [Session], a.DateTime, i.address [IP], i.NumProblems Problems, b.domain [Blacklist], i.isSpider, i.Hits, a.Request, a.Referrer, a.UserAgent, sr.Engine, sr.Query from activities a \r\n" +
				"inner join sessions s on a.sid=s.id \r\n" +
				"inner join ips i on s.ipid=i.id \r\n" +
				"left outer join SearchResults sr on sr.aid=a.id \r\n" +
				"left outer join blacklist b on a.referrer like '%' + b.domain+'%' \r\n" +
				"order by a.id desc");

			Dictionary<int, int> ActivityToHits = new Dictionary<int, int>();
			for (int i = 0; i < dgActivities.Rows.Count; i++)
			{
				DataGridViewRow dr = dgActivities.Rows[i];
				int hits = (int)dr.Cells["Hits"].Value;
				int AID = (int)dr.Cells["Activity"].Value;
				if (ActivityToHits.ContainsKey(AID)) {
					hits--;
					ActivityToHits[AID]++; }
				else ActivityToHits.Add(AID, hits);
				dr.Cells["Hits"].Value = hits;

				if ((int)dr.Cells["Activity"].Value > LastActivityID) {
					if ((int)dr.Cells["Activity"].Value > TempLastActivityID)
						TempLastActivityID = (int)dr.Cells["Activity"].Value;
					if (LastActivityID > 0)
						dr.DefaultCellStyle.BackColor = Color.LimeGreen; }
				else if (((string)dr.Cells["IP"].Value).Trim().StartsWith("192.168"))
					dr.DefaultCellStyle.BackColor = Color.LightPink;
				else if (dr.Cells["Blacklist"].Value.GetType() != typeof(DBNull) && ((string)dr.Cells["Blacklist"].Value).Trim().Length > 0)
					dr.DefaultCellStyle.BackColor = Color.Red;
				else if (((int)dr.Cells["Problems"].Value) > 0)
					dr.DefaultCellStyle.BackColor = Color.Orange;
				else if ((bool)dr.Cells["isSpider"].Value) {
					dr.DefaultCellStyle.BackColor = Color.Black;
					dr.DefaultCellStyle.ForeColor = Color.White; }
				else if (hits == 1)
					dr.DefaultCellStyle.BackColor = Color.LightBlue;

				if (dr.Cells["Query"].Value.GetType() != typeof(DBNull) && ((string)dr.Cells["query"].Value).Trim().Length > 0)
					dr.Cells["Query"].Style.BackColor = Color.LightGreen;
				else if (dr.Cells["Referrer"].Value.GetType() != typeof(DBNull) && !((string)dr.Cells["Referrer"].Value).Trim().ToLower().Contains("kconnolly.net"))
					dr.Cells["Referrer"].Style.BackColor = Color.LightGreen;
			}
			if (TempLastActivityID > 0) LastActivityID = TempLastActivityID;
			dgActivities.DefaultCellStyle.Font = new System.Drawing.Font(FontFamily.GenericSerif, 7);
			foreach (DataGridViewColumn dc in dgActivities.Columns)
				dc.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
			dgActivities.Columns["Referrer"].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
			dgActivities.Columns["DateTime"].Width = 50;
			dgActivities.Columns["IP"].Width = 70;
			dgActivities.Columns["Hits"].Width = 30;
			dgActivities.Columns["Engine"].Width = 50;
			dgActivities.Columns["Query"].Width = 150;
			dgActivities.Columns["Request"].Width = 300;
			dgActivities.Columns["UserAgent"].Width = 200;
			dgActivities.Columns["Activity"].Visible = false;
			dgActivities.Columns["Session"].Visible = false;
			dgActivities.Columns["isSpider"].Visible = false;
			dgActivities.Columns["Problems"].Visible = false;
			dgActivities.Columns["Blacklist"].Visible = false;
			dgActivities.ClearSelection();

		}
		private void LoadSearchGrid()
		{
			// Search Results
			dgSearches.DataSource = KC.DataAccess.Global.Global.CallSql_Query(csMv8, "select top 50 sr.ID SRID, sr.DateTime, i.Address, Engine, Query from searchresults sr \r\n" +
				"inner join Activities a on sr.aid=a.id \r\n" +
				"inner join Sessions s on s.id=a.sid \r\n" +
				"inner join IPs i on s.ipid=i.id \r\n" +
				"order by sr.id desc ");
			foreach (DataGridViewColumn dc in dgSearches.Columns)
				dc.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
			dgSearches.DefaultCellStyle.Font = new System.Drawing.Font(FontFamily.GenericSerif, 7);
			dgSearches.Columns["SRID"].Visible = false;
			dgSearches.Columns["Address"].Visible = false;
			dgSearches.Columns["DateTime"].Width = 96;
			dgSearches.Columns["Engine"].Width = 45;
			dgSearches.Columns["Query"].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
			dgSearches.ClearSelection();
		}
		private void LoadSocialGrid()
		{
			dgSocial.DataSource = KC.DataAccess.Global.Global.CallSql_Query(csSocial, "select top 50 ID, thirdpartydatetime [Time], Site, ActivityURL, ActivityDescription [Activity] from ContactActivities order by id desc");
			foreach (DataGridViewColumn dc in dgSocial.Columns)
				dc.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
			dgSocial.DefaultCellStyle.Font = new System.Drawing.Font(FontFamily.GenericSerif, 7);
			dgSocial.Columns["ID"].Visible = false;
			dgSocial.Columns["Time"].Width = 96;
			dgSocial.Columns["Site"].Width = 60;
			dgSocial.Columns["ActivityURL"].Width = 150;
			dgSocial.Columns["ActivityURL"].DefaultCellStyle.Font = new System.Drawing.Font(FontFamily.GenericSerif, 6);
			dgSocial.Columns["ActivityURL"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
			dgSocial.Columns["Activity"].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
		}

		private void ProcessYoutubeActivity(Google.YouTube.Activity act)
		{
			KC.DataAccess.Global.Global.CallSql_Query(csSocial, "insert into ContactActivities (ThirdPartyID, ThirdPartyDatetime, Datetime, Site, SiteURL, ContactName, ContactURL, ActivityDescription, ActivityDetail, ActivityURL, AdditionalDetails) " +
				"values ('" + act.Id.Trim().Replace("'", "''") + "', '" + act.Updated.ToString("MM/dd/yyyy HH:mm:ss") + "', getdate(), 'YouTube', 'http://www.youtube.com', '" + act.Author.Replace("'", "''") +
				"', 'http://www.youtube.com/" + act.Author.Replace("'", "''") + "','" + act.Title.Replace("'", "''") + "', '', '" + ((act.VideoId == null || act.VideoId.Trim().Length == 0) ? act.AtomEntry.SelfUri.Content.Replace("'", "''") : ("http://www.youtube.com/watch?v=" + act.VideoId.Trim()))
				+ "', '" + act.Self.Replace("'", "''") + "')");
		}
		private void bgwYoutubeLookups_DoWork(object sender, DoWorkEventArgs e)
		{
			DownloadYoutubeActivity();
		}

		private void tSpider_Tick(object sender, EventArgs e)
		{
			DataTable dt = KC.DataAccess.Global.Global.CallSql_Query(csMv8, "update activities set isDetectionComplete=1 where id in (select distinct top 50 a.id from activities a inner join sessions s on a.sid=s.id inner join ips i on s.ipid=i.id where isspider=1 order by id desc); " +
				"select top 50 IPID, isSpider, a.* from activities a inner join sessions s on a.sid=s.id inner join ips i on s.ipid=i.id where address not like '192.168.%' and address not like '10.%' and a.datetime>'2012-02-01' order by isdetectioncomplete, id desc");
			for (int i = 0; i < dt.Rows.Count; i++)
			{
				bool isSpider = false;
				string useragent = dt.Rows[i]["useragent"].ToString().ToLower().Trim();
				if (useragent.Contains("spider")
					|| useragent.Contains("bot")
					|| useragent.Contains("google")
					|| useragent.Contains("networkquality")
					|| useragent.Contains("ichiro")
					|| useragent.Contains("longurl")
					|| useragent.Contains("resolver")
					|| useragent.Contains("crawler")
					|| useragent.Contains("fetchor")
					|| useragent.Contains("summify")
					|| useragent.Contains("wget")
					|| useragent.Contains("facebookexternalhit")
					|| useragent.Contains("python-httplib2")
					|| useragent.Contains("api")
					|| useragent.Contains("ning/")
					|| useragent.Contains("postrank")
					|| useragent.Contains("eventmachine")
					|| useragent.Contains("readability")
					|| useragent.Contains("postrank")
					|| useragent.Contains("miner")
					|| useragent.Contains("ruby")
					|| useragent.Contains("untiny")
					|| useragent.Contains("scraper")
					|| useragent.Contains("crowsnest")
					|| useragent.Contains("nojs")
					|| useragent.Contains("twikle")
					|| useragent.Contains("getter")
					|| useragent.Contains("python")
					|| useragent.Contains("java/")
					|| useragent.Trim().Length == 0
					)
					isSpider = true;

				KC.DataAccess.Global.Global.CallSql_Query(csMv8, "update activities set isDetectionComplete=1 where id=" + dt.Rows[i]["ID"].ToString());
				if (isSpider) KC.DataAccess.Global.Global.CallSql_Query(csMv8, "update IPs set isSpider=1 where id=" + dt.Rows[i]["IPID"].ToString());
			}
		}
	}
}
